package com.cinema.dao.imp;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cinema.Bean.Hall;
import com.cinema.Bean.Movie;
import com.cinema.Bean.MsgStatistical;
import com.cinema.dao.BaseDao;
import com.cinema.dao.IMovieDao;
import com.mysql.jdbc.PreparedStatement;

/**
 * 用户表的mysql数据库实现类
 * **/

public class MovieDaoMysql extends BaseDao implements IMovieDao {
	public int insert(Movie a) {
		String sql = "insert into movie values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		String[] args = { a.getMovieId(),a.getMovieName(),a.getStarring(),a.getDetail(),
				a.getDuration()+"",a.getDirector(),a.getLanguage(),a.getMovieType(),
				a.getSlogan(),a.getPoster(),a.getManActorPic(),a.getWomanActorPic(),
				a.getReleaseTime(),a.getMovieStat()+""};
		return change(sql, args);
	}

	public int delete(Movie a) {
		String sql = "delete from movie where movieId=?";
		String[] args = { a.getMovieId() };
		return change(sql, args);
	}

	public int update(Movie a) {
		String sql = "update movie set movieName=?,starring=?,detail=?,duration=?,"
				+ "director=?,language=?,movieType=?,slogan=?,poster=?,manActorPic=?,"
				+ "womanActorPic=?,releaseTime=?,movieStat=? where movieId=?";
		String[] args = {a.getMovieName(),a.getStarring(),a.getDetail(),
				a.getDuration()+"",a.getDirector(),a.getLanguage(),a.getMovieType(),
				a.getSlogan(),a.getPoster(),a.getManActorPic(),a.getWomanActorPic(),
				a.getReleaseTime(),a.getMovieStat()+"",a.getMovieId() };
		return change(sql, args);
	}

	/**
	 * 根据id进行查询
	 */
	public Movie SelectById(String id) {
		// TODO Auto-generated method stub
		Movie a = null;
		String sql = "select * from movie where movieId=?";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
			pstmt.setString(1, id);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				a = new Movie();
				a.setMovieId(rs.getString("movieId"));
				a.setMovieName(rs.getString("movieName"));
				a.setStarring(rs.getString("starring"));
				a.setDetail(rs.getString("detail"));
				a.setDuration(rs.getInt("duration"));
				a.setDirector(rs.getString("director"));
				a.setLanguage(rs.getString("language"));
				a.setMovieType(rs.getString("movieType"));
				a.setSlogan(rs.getString("slogan"));
				a.setPoster(rs.getString("poster"));
				a.setManActorPic(rs.getString("manActorPic"));
				a.setWomanActorPic(rs.getString("womanActorPic"));
				a.setReleaseTime(rs.getString("releaseTime"));
				a.setMovieStat(rs.getInt("movieStat"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return a;
	}

	/**
	 * 获取所有
	 */
	public List<Movie> selectAll() {
		// TODO Auto-generated method stub
		List<Movie> list = new ArrayList<Movie>();
		Movie a = null;
		String sql = "select * from movie";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con
					.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				a = new Movie();
				a.setMovieId(rs.getString("movieId"));
				a.setMovieName(rs.getString("movieName"));
				a.setStarring(rs.getString("starring"));
				a.setDetail(rs.getString("detail"));
				a.setDuration(rs.getInt("duration"));
				a.setDirector(rs.getString("director"));
				a.setLanguage(rs.getString("language"));
				a.setMovieType(rs.getString("movieType"));
				a.setSlogan(rs.getString("slogan"));
				a.setPoster(rs.getString("poster"));
				a.setManActorPic(rs.getString("manActorPic"));
				a.setWomanActorPic(rs.getString("womanActorPic"));
				a.setReleaseTime(rs.getString("releaseTime"));
				a.setMovieStat(rs.getInt("movieStat"));
				list.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 根据传入条件进行查询
	 * @param where 条件
	 * @return list对象
	 */
	public List<Movie> selectByWhere(String where) {
		// TODO Auto-generated method stub
		List<Movie> list = new ArrayList<Movie>();
		Movie a = null;
		String sql = "select * from movie where 1=1 "; // where字句
		if(where!=null && !where.trim().equals("")) {
			sql+="and "+where;
		}
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con
					.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				a = new Movie();
				a.setMovieId(rs.getString("movieId"));
				a.setMovieName(rs.getString("movieName"));
				a.setStarring(rs.getString("starring"));
				a.setDetail(rs.getString("detail"));
				a.setDuration(rs.getInt("duration"));
				a.setDirector(rs.getString("director"));
				a.setLanguage(rs.getString("language"));
				a.setMovieType(rs.getString("movieType"));
				a.setSlogan(rs.getString("slogan"));
				a.setPoster(rs.getString("poster"));
				a.setManActorPic(rs.getString("manActorPic"));
				a.setWomanActorPic(rs.getString("womanActorPic"));
				a.setReleaseTime(rs.getString("releaseTime"));
				a.setMovieStat(rs.getInt("movieStat"));
				list.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 根据关键词进行查询
	 */
	public List<Movie> Select(String keyword) {
		// TODO Auto-generated method stub
		List<Movie> list = new ArrayList<Movie>();
		Movie a = null;
		String sql = "select * from movie where movieId like '%" + keyword
		+ "%' or movieName like '%" + keyword
		+ "%' or language like '%" + keyword
		+ "%' or movieType like '%" + keyword
		+ "%' or starring like '%" + keyword + "%'";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con
					.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				a = new Movie();
				a.setMovieId(rs.getString("movieId"));
				a.setMovieName(rs.getString("movieName"));
				a.setStarring(rs.getString("starring"));
				a.setDetail(rs.getString("detail"));
				a.setDuration(rs.getInt("duration"));
				a.setDirector(rs.getString("director"));
				a.setLanguage(rs.getString("language"));
				a.setMovieType(rs.getString("movieType"));
				a.setSlogan(rs.getString("slogan"));
				a.setPoster(rs.getString("poster"));
				a.setManActorPic(rs.getString("manActorPic"));
				a.setWomanActorPic(rs.getString("womanActorPic"));
				a.setReleaseTime(rs.getString("releaseTime"));
				a.setMovieStat(rs.getInt("movieStat"));
				list.add(a);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 得到所有的影片以及上映时间
	 * @return MsgStatistical对象
	 */
	public List<MsgStatistical> selectByStatWhere() {
		// TODO Auto-generated method stub
		MsgStatistical m = null;
		List<MsgStatistical> list = new ArrayList<MsgStatistical>();
		String sql = "select \r\n" + 
				"	COUNT(m.movieId) AS \"countMovie\",\r\n" + 
				"	date_format(m.releaseTime, '%Y-%m-%d' ) as \"releaseTime\"\r\n" + 
				"from movie as m\r\n" + 
				"GROUP BY date_format(m.releaseTime, '%Y-%m-%d' )\r\n" + 
				"ORDER BY m.releaseTime \r\n" + 
				"";
		Connection con = getCon();
		try {
			PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				m = new MsgStatistical();
				m.setCount(rs.getString("countMovie"));
				m.setTime(rs.getString("releaseTime"));
				list.add(m);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}
